package com.dao.impl;

import com.bean.Student;
import com.dao.StudentDAO;
import com.util.C3p0Utils;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 *
 */
public class StudentDAOImpl implements StudentDAO {
    /**
     * BeanListHandler将结果列表封装
     * @return
     * @throws SQLException
     */
    @Override
    public List<Student> selectAll() throws SQLException {

        return C3p0Utils.qr.query("select * from person", new BeanListHandler<Student>(Student.class));
    }

    /**
     * BeanHandler将单个列表封装
     * @param id
     * @return
     * @throws SQLException
     */
    @Override
    public Student selectById(Integer id) throws SQLException {
        return C3p0Utils.qr.query("select * from person where id =?", new BeanHandler<>(Student.class), id);
    }

    @Override
    public boolean insert(Student stu) throws SQLException {
        return C3p0Utils.qr.update("insert into person values(null,?,?,?)", stu.getName(), stu.getAge(), stu.getGender()) > 0;
    }

    @Override
    public boolean deleteById(Integer id) throws SQLException {
        return C3p0Utils.qr.update("delete from person where id=?", id) > 0;
    }

    @Override
    public boolean update(Student stu) throws SQLException {
        return C3p0Utils.qr.update("update person set name=?,age=?,gender=? where id=?", stu.getName(), stu.getAge(), stu.getGender(), stu.getId()) > 0;
    }

    /**
     * ScalarHandler用来将单个Object类型封装
     * @return
     * @throws SQLException
     */
    @Override
    public Integer count() throws SQLException {
        int n = C3p0Utils.qr.query("select count(*) from person", new ScalarHandler<Long>()).intValue();
        return n;
    }

    @Override
    public Integer countSearch(Map map) throws SQLException {
        StringBuffer sql = new StringBuffer("select count(*) from person where 1=1");
        if(map==null){
            return C3p0Utils.qr.query(sql.toString(),new ScalarHandler<Long>()).intValue();
        }
        Set<Map.Entry<String,String>> set = map.entrySet();
        //list存放值  map来取属性
        List list = new ArrayList();
        for(Map.Entry<String,String> entry: set ){
            String key = entry.getKey();
            String value = entry.getValue();
            sql.append("and"+key+"like ?");
            list.add("%"+value+"%");
        }
        return C3p0Utils.qr.query(sql.toString(),new ScalarHandler<Long>(),list.toArray()).intValue();
    }

    @Override
    public boolean deleteAll(String[] idsArr) throws SQLException {
        StringBuffer sql = new StringBuffer("delete from person where id in (");
        for (int i = 0; i < idsArr.length; i++) {
            sql.append("?,");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(")");
        return C3p0Utils.qr.update(sql.toString(), idsArr) > 0;
    }

    @Override
    public List<Student> selectAllPagination(Integer offset, Integer limit) throws SQLException {
        return C3p0Utils.qr.query("select * from person limit ?,?", new BeanListHandler<>(Student.class), offset, limit);

    }

    @Override
    public List<Student> selectAllPaginationSearch(Integer offset, Integer limit,  Map map) throws SQLException {
        StringBuffer sql = new StringBuffer("select * from person where 1=1");
        if(map==null){
            sql.append(" limit ?,?");
            return C3p0Utils.qr.query(sql.toString(),new BeanListHandler<>(Student.class),offset,limit);
        }
        Set<Map.Entry<String,String>> set = map.entrySet();
        //list存放值  map来取属性
        List list = new ArrayList();
        for(Map.Entry<String,String> entry: set ){
            String key = entry.getKey();
            String value = entry.getValue();
            sql.append("and"+key+"like ?");

            list.add("%"+value+"%");
        }
        sql.append(" limit ?,?");
        list.add(offset);
        list.add(limit);
        return C3p0Utils.qr.query(sql.toString(),new BeanListHandler<>(Student.class),list.toArray());
    }
}

